import os
import pyodbc
import pandas as pd
import Get_HTMLtables as GH
import numpy as np
from _sqlite3 import connect
import openpyxl
from openpyxl.reader.excel import load_workbook
global tables
import pyodbc
import re
import os
class PDFTableExtractor:
    def __init__(self, ID, find_keyword,Html_file):
        self.ID = ID
        read_html_obj = GH.ReadHtml()  # 创建GH.ReadHtml实例
        self.ZBDW=read_html_obj.read_DW(Html_file,find_keyword)
        try:
             self.BTSFXX=read_html_obj.read_BTSFXX(Html_file,find_keyword)
        except:
             self.BTSFXX='匹配不到，自己找一下'
    def get_EPBH(self):
        server = 'SHPUBSRV02'
        database = 'JYCFI'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        query = """
                   SELECT DISTINCT EPBH
                   FROM [10.106.22.51].JYPRIME.dbo.usrQYMB GG 
                   WHERE QYBH= ?
                       """
        IGSDM = str(self.IGSDM)
        self.EPBH = pd.read_sql(query, conn, params=[IGSDM])
        try:
            self.EPBH = self.EPBH['EPBH'][0]
        except:
            self.EPBH = 'EP未匹配成功，需要人工处理'

            return self.EPBH

    def get_GG(self):
        server = 'SHPUBSRV01'
        database = 'JYPRIME'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        ID = self.ID
        query = """
                                  SELECT ID,IGSDM,CONVERT(varchar(10),XXFBRQ,23) AS XXFBRQ,CONVERT(varchar(10),JZRQ,23) AS JZRQ,XXBT,GGLJ
                                  FROM usrGSGGYWFWB GG 
                                  WHERE ID= CAST(? AS NVARCHAR)
                                      """
        self.GG = pd.read_sql(query, conn, params=[ID])
        if not self.GG.empty:
            self.IGSDM = self.GG['IGSDM'][0]
            self.XXFBRQ = self.GG['XXFBRQ'][0]
            self.JZRQ = self.GG['JZRQ'][0]
            self.XXLY = self.GG['XXBT'][0] 
            return self.GG
    def find_keyword_and_extract_table(self, Html_file, find_keyword):
        table = GH.ReadHtml.read_html_file(Html_file, find_keyword)
        if table.shape[1]!='0':
            table = table.replace('主要产品', 'SJLMSMC').replace(' ','',regex=True).replace('%','',regex=True)
            try :
                if table[0][0] != '分行业情况' and table[0][0] == '分行业':
                    VALUE = ['分行业情况', '', '', '', '', '', '']
                    table.loc[-1] = VALUE
                    table.index = table.index + 1  # 更新索引
                    table = table.sort_index()  # 根据索引排序
                elif table[0][0] != '分产品情况' and table[0][0] == '分产品':
                    VALUE = ['分产品情况', '', '', '', '', '', '']
                    table.loc[-1] = VALUE
                    table.index = table.index + 1  # 更新索引
                    table = table.sort_index()  # 根据索引排序
            except:
                pass
        return table
    # 切割表格(营收)
    def sp(self,table):
        keywords = ['分行业情况','分产品情况']
        print('开始分割')
        if any(table.isin(['分行业情况', '分产品情况'])):
            new_df = table
            new_df.columns=range(len(new_df.columns))
            BASCI_split_indices = [0]
            new_df[0] = new_df[0].astype(str)
            split_indices = new_df[
                new_df[0].apply(lambda x: any(keyword in x for keyword in keywords))].index.tolist()
            split_indices = [int(str(index).replace('[', '').replace(']', '')) for index in split_indices]
            split_indices = BASCI_split_indices + split_indices
            if split_indices:
                split_indices.append(len(new_df))
                with pd.ExcelWriter('分割表格.xlsx', engine='xlsxwriter') as writer:
                    for i in range(0, len(split_indices)):
                        if split_indices[i] > 0:
                            dfs = new_df.iloc[split_indices[i - 1] + 1:split_indices[i]]
                            sheet_name = dfs[0].iloc[0]
                            dfs.to_excel(writer, sheet_name=sheet_name, index=False)

    def xggs(self):
        excel_file_path = '分割表格.xlsx'
        new_excel_file_path = f'成本分析表\\上交所\\{self.EPBH}_{self.JZRQ}_成本分析主表.xlsx'
        workbook = openpyxl.load_workbook(excel_file_path)
        sheet_names = workbook.sheetnames
        with pd.ExcelWriter(new_excel_file_path, engine='openpyxl') as writer:
            all=pd.DataFrame()
            for i, sheet_name in enumerate(sheet_names, start=1):
                print(f'Processing sheet {i}')
                yysr = pd.read_excel(excel_file_path, sheet_name=sheet_name).replace(' ', '', regex=True).replace('/','')
                new_columns = ['JYXMMCYS', 'CBGCXMYS', 'BQCBJE','BQCBZZCBBL','SNTQCBJE','SNTQCBZZCBBL','BQCBTBBD','BZSM']
                yysr = yysr.rename(columns=dict(zip(yysr.columns, new_columns)))
                previous_value = None
                for index, row in yysr.iterrows():
                    if row['JYXMMCYS'] == '小计':
                        yysr.at[index, 'JYXMMCYS'] = previous_value
                        yysr.at[index, 'CBGCXMYS'] = '小计'
                    else:
                        previous_value = row['JYXMMCYS']
                    if (row['JYXMMCYS'] == '其他' or row['JYXMMCYS'] == '合计') and (row['CBGCXMYS']=='合计'or row['CBGCXMYS']=='小计'or pd.isnull(row['CBGCXMYS'] )) :
                        if'营业成本'in yysr['SNTQCBZZCBBL'].iloc[0]:
                            yysr.at[index, 'CBGCXMYS'] = '营业成本'
                        elif'总成本' in yysr['SNTQCBZZCBBL'].iloc[0]:
                            yysr.at[index, 'CBGCXMYS'] = '总成本'
                    if  (row['JYXMMCYS'] == ''or row['JYXMMCYS'] == None or pd.isna(row['JYXMMCYS']) ) and (row['CBGCXMYS']=='合计'or row['CBGCXMYS']=='小计'or pd.isnull(row['CBGCXMYS'] )):
                        yysr.at[index, 'JYXMMCYS'] = yysr.at[index-1, 'JYXMMCYS']
                yysr['FLFSMC']=None
                yysr['FLFS']=None
                yysr['IGSDM']=None
                yysr['IGSDM'].iloc[:len(yysr)]=self.IGSDM
                yysr['XXFBRQ']=None
                yysr['XXFBRQ'].iloc[:len(yysr)]=self.XXFBRQ
                yysr['JZRQ']=None
                yysr['JZRQ'].iloc[:len(yysr)]=self.JZRQ
                yysr['XXLY']=None
                yysr['XXLY'].iloc[:len(yysr)]=self.XXLY
                yysr['XXLYBM']=None
                yysr['XXLYBM'].iloc[:len(yysr)]='FCC00000005E'
                yysr['BZ']=None
                yysr['BZ'].iloc[:len(yysr)]='FCC000000015'
                yysr['ZBDW']=None
                yysr['ZBDW']=self.ZBDW
                yysr['BTSFXX']=None
                yysr.loc[:len(yysr),'BTSFXX']=self.BTSFXX
                if yysr.iloc[0][0]=='分行业':
                    yysr.loc[:len(yysr),'FLFSMC']='按行业'
                    yysr.loc[:len(yysr),'FLFS']='10'
                elif yysr.iloc[0][0]=='分产品':
                    yysr.loc[:len(yysr),'FLFSMC']='按产品'
                    yysr.loc[:len(yysr),'FLFS']='20'
                yysr = yysr.drop(0)
                all=pd.concat([all,yysr])
                all = all.reindex(
                    columns=['IGSDM', 'XXFBRQ', 'JZRQ', 'XXLY', 'XXLYBM', 'FLFS', 'FLFSMC', 'JYXMMCYS',
                             'CBGCXMYS', 'BQCBJE', 'ZBDW', 'BQCBZZCBBL','SNTQCBJE', 'SNTQCBZZCBBL', 'BQCBTBBD','YSHXH','BTSFXX','BZ', 'BZSM'])
            all.to_excel(writer, index=False, sheet_name=sheet_name)
            return all
def run_all(ID, Html_file):
    Html_file = Html_file
    find_keyword = [['成本构成项目', '分产品'], ['成本构成项目', '分项目'], ['本期占总成本比例', '分产品'],
                    ['本期占总成本比例', '分行业']]
    extractor_instance = PDFTableExtractor(ID=ID, find_keyword=find_keyword, Html_file=Html_file)
    extractor_instance.get_GG()
    extractor_instance.get_EPBH()
    table = extractor_instance.find_keyword_and_extract_table(Html_file=Html_file, find_keyword=find_keyword)
    if table.shape[1] != 0:
        extractor_instance.sp(table)
        table = extractor_instance.xggs()
        table['YSHXH'] = None
        table['YSHXH'] = range(1, len(table) + 1)
        return table
    else:
        print('未找到关键词')
    return pd.DataFrame()
if __name__ == '__main__':
    ID = '767387502893'
    Html_file = r'D:\软件打包\Juno-win32.win32.x86_64\Juno-win32.win32.x86_64\767387502893.HTML'
    run_all(ID, Html_file)